package com.jiepos.mpos.biz.customer.service;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.jiepos.mpos.common.constant.DBConstant;
import com.jiepos.mpos.common.constant.DBConstant.Table;
import com.jiepos.mpos.common.constant.SystemConstant;
import com.jiepos.mpos.common.dao.AppCustomBankcardDao;
import com.jiepos.mpos.common.dao.AppCustomerInfoDao;
import com.jiepos.mpos.common.dao.CardBinDao;
import com.jiepos.mpos.common.entity.App_Custom_BankcardEntity;
import com.jiepos.mpos.common.entity.GpsEntity;
import com.jiepos.mpos.core.entity.Pagination;
import com.jiepos.mpos.core.service.BaseService;
import com.jiepos.mpos.core.util.CollectionUtil;
import com.jiepos.mpos.core.util.IDFactoryUtil;
import com.jiepos.mpos.core.util.StringUtils;

/**
 * 
 * 会员银行卡业务类
 * 
 * @ProjectName: [mpos1]
 * @Package com.jiepos.mpos.biz.customer.service
 * @author 肖志伟
 * @version 1.0
 * @since 2017年9月22日 下午4:42:05
 * @history（历次修订内容、修订人、修订时间等） @UpdateRemark [说明本次修改内容]
 * @Copyright (c) 2017 江苏杰博实信息技术有限公司
 */
@Service
public class CustomBankcardInfoService extends BaseService {
	
	@Autowired
	private AppCustomBankcardDao appCustomBankcardDao;
	@Autowired
	private CardBinDao cardBinDao;
	@Autowired
	private AppCustomerInfoDao appCustomerInfoDao;

	public Map getCustomBankcardListJson(HttpServletRequest request,Pagination pagination,
			String custom_no,String phone,String name,String settle_bank_no,String account_attr,String settle_acct,String audit_st){
		StringBuffer sb = this.getInitSql("t1");
		sb.append(",T1.CUSTOM_UUID,T1.CUSTOM_NO,nvl2(T2.SUPER_BANK_NO,T2.SUPER_BANK_NO || '-' || T2.SUPER_BANK_NM,'') as super_bank_no,nvl2(T2.SETTLE_BANK_NO,T2.SETTLE_BANK_NO || '-' || T2.BANK_NM,'') as settle_bank_no,")
			.append("CONCAT (RPAD (SUBSTR (T1.login_id, 1, 3),LENGTH (T1.login_id) - 4,'*'),SUBSTR (T1.login_id,LENGTH (T1.login_id) - 3,4)) ||'-'||T1.\"NAME\" as CUSTOM_NAME,")
			.append("(select code_val||'-'||code_desc from "+Table.T_APP_CODE_LIST +" where code_tp = '"+DBConstant.Code.CARD_TYPE+"' and code_val = t2.acct_attr) as account_attr,")
			.append("(select code_val||'-'||code_desc from T_APP_CODE_LIST where code_tp = '"+DBConstant.Code.AUDIT_ST+"' and code_val = t2.AUDIT_ST)  as AUDIT_ST,")
			.append(" CONCAT (RPAD (SUBSTR (T2.settle_acct, 1, 6),LENGTH (T2.settle_acct) - 4,'*'),SUBSTR (T2.settle_acct,LENGTH (T2.settle_acct) - 3,4)) as SETTLE_ACCT ")
			.append(" from ").append(Table.T_APP_CUSTOM_INF).append(" t1 LEFT JOIN ").append(Table.T_APP_CUSTOM_BANKCARD)
			.append(" t2 on t1.CUSTOM_UUID = T2.CUSTOM_UUID ").append(" where t1.rec_st != '0' and t2.rec_st != '0' ");
		
		List list = new ArrayList<>();
		if(!StringUtils.isBlank(custom_no)){
			sb.append(" and t1.custom_no like ?");
			list.add("%"+custom_no+"%");
		}
		if(!StringUtils.isBlank(phone)){
			sb.append(" and t1.phone like ?");
			list.add("%"+phone+"%");
		}
		if(!StringUtils.isBlank(name)){
			sb.append(" and t1.name like ?");
			list.add("%"+name+"%");
		}
		if(!StringUtils.isBlank(settle_bank_no)){
			sb.append(" and t2.settle_bank_no like ?");
			list.add("%"+settle_bank_no+"%");
		}
		if(!StringUtils.isBlank(account_attr)){
			sb.append(" and t2.acct_attr = ?");
			list.add(account_attr);
		}
		if(!StringUtils.isBlank(settle_acct)){
			sb.append(" and t2.settle_acct like ?");
			list.add("%"+settle_acct+"%");
		}
		if(!StringUtils.isBlank(audit_st)){
			sb.append(" and t2.audit_st = ?");
			list.add(audit_st);
		}
		
		pagination.queryString = sb.toString();
		pagination = super.getCustomAuthSql(request, pagination, "t1", null);
		pagination = this.getOrderSql(pagination, "t1");
		return appCustomBankcardDao.getPaginationMap(pagination, "t1", list);
	}
	
	/**
	 * 获取会员的银行卡的信息（只显示最新的数据）
	 *
	 * @param custom_uuid
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Map getCustomBankCard(String custom_uuid) {
		StringBuffer sb = new StringBuffer();
		sb.append("select t1.bankcard_uuid,t1.custom_uuid,t1.super_bank_nm,t1.settle_bank_no,t1.bank_nm,")
			.append(" t1.settle_acct,t1.acct_nm,t2.area_cn_nm as \"bank_province\", t3.area_cn_nm as \"bank_locality\", ")
			.append(" t1.acct_attr,t1.audit_st,t1.rec_st,t1.rec_id from ").append(Table.T_APP_CUSTOM_BANKCARD)
			.append(" t1 LEFT JOIN "+Table.T_APP_AREA_CD+" t2 on t1.bank_province = t2.area_cd ")
			.append(" left join "+Table.T_APP_AREA_CD+" t3 on t1.bank_locality = t3.area_cd")
			.append(" where t1.custom_uuid = ? and t1.audit_st = '"+SystemConstant.REVIEW_ST_1 
					+ "' and t1.acct_attr = ? and t1.rec_st != ? ORDER BY t1.rec_id DESC");
		List list = new ArrayList();
		list.add(custom_uuid);
		list.add(SystemConstant.ACCT_ATTR_0);
		list.add(SystemConstant.REC_ST_0);
		List<Map<String, Object>> result = appCustomBankcardDao.queryForList(sb.toString(), list);
		if (!CollectionUtil.isEmpty(result)) {
			return result.get(0);
		} else {
			StringBuffer sb1 = new StringBuffer();
			sb1.append("select t1.bankcard_uuid,t1.custom_uuid,t1.super_bank_nm,t1.settle_bank_no,t1.bank_nm,")
				.append(" t1.settle_acct,t1.acct_nm,t2.area_cn_nm as \"bank_province\", t3.area_cn_nm as \"bank_locality\", ")
				.append(" t1.acct_attr,t1.audit_st,t1.rec_st,t1.rec_id from ").append(Table.T_APP_CUSTOM_BANKCARD)
				.append(" t1 LEFT JOIN "+Table.T_APP_AREA_CD+" t2 on t1.bank_province = t2.area_cd ")
				.append(" left join "+Table.T_APP_AREA_CD+" t3 on t1.bank_locality = t3.area_cd")
				.append(" where t1.custom_uuid = ? and t1.acct_attr = ? and t1.rec_st != ? ORDER BY t1.rec_id DESC");
			List list1 = new ArrayList();
			list1.add(custom_uuid);
			list1.add(SystemConstant.ACCT_ATTR_0);
			list1.add(SystemConstant.REC_ST_0);
			List<Map<String, Object>> result1 = appCustomBankcardDao.queryForList(sb1.toString(), list1);
			if (!CollectionUtil.isEmpty(result1)) {
				return result1.get(0);
			}else{
				return null;
			}
		}
	}

	/**
	 * 获取会员的要变更的银行卡
	 *
	 * @param custom_uuid
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Map getNewestCustomBankcard(String custom_uuid) {
		StringBuffer sb = new StringBuffer();
		sb.append("select t1.bankcard_uuid,t1.custom_uuid,t1.super_bank_nm,t1.settle_bank_no,t1.bank_nm,")
			.append(" t1.settle_acct,t1.acct_nm,t2.area_cn_nm as \"bank_province\", t3.area_cn_nm as \"bank_locality\", ")
			.append(" t1.acct_attr,t1.audit_st,t1.rec_st,t1.rec_id from ").append(Table.T_APP_CUSTOM_BANKCARD)
			.append(" t1 LEFT JOIN "+Table.T_APP_AREA_CD+" t2 on t1.bank_province = t2.area_cd ")
			.append(" left join "+Table.T_APP_AREA_CD+" t3 on t1.bank_locality = t3.area_cd")
			.append(" where t1.custom_uuid = ? and t1.audit_st in ("+SystemConstant.REVIEW_ST_0+") and t1.acct_attr = ? and t1.rec_st != ?");
		List list = new ArrayList();
		list.add(custom_uuid);
		list.add(SystemConstant.ACCT_ATTR_0);
		list.add(SystemConstant.REC_ST_0);
		List<Map<String, Object>> result = appCustomBankcardDao.queryForList(sb.toString(), list);
		if (!CollectionUtil.isEmpty(result)) {
			return result.get(0);
		} else {
			return null;
		}

	}

	/**
	 * 获取会员的已有的银行卡
	 *
	 * @param custom_uuid
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Map getOldCustomBankcard(String custom_uuid) {
		StringBuffer sb = new StringBuffer();
		sb.append("select t1.bankcard_uuid,t1.custom_uuid,t1.super_bank_nm,t1.settle_bank_no,t1.bank_nm,")
			.append(" t1.settle_acct,t1.acct_nm,t2.area_cn_nm as \"bank_province\", t3.area_cn_nm as \"bank_locality\", ")
			.append(" t1.acct_attr,t1.audit_st,t1.rec_st,t1.rec_id from ").append(Table.T_APP_CUSTOM_BANKCARD)
			.append(" t1 LEFT JOIN "+Table.T_APP_AREA_CD+" t2 on t1.bank_province = t2.area_cd ")
			.append(" left join "+Table.T_APP_AREA_CD+" t3 on t1.bank_locality = t3.area_cd")
			.append(" where t1.custom_uuid = ? and t1.audit_st in ("+SystemConstant.REVIEW_ST_1+") and t1.acct_attr = ? and t1.rec_st != ?");
		List list = new ArrayList();
		list.add(custom_uuid);
		list.add(SystemConstant.ACCT_ATTR_0);
		list.add(SystemConstant.REC_ST_0);
		List<Map<String, Object>> result = appCustomBankcardDao.queryForList(sb.toString(), list);
		if (!CollectionUtil.isEmpty(result)) {
			return result.get(0);
		} else {
			return null;
		}

	}
	
	/**
	 * 获取实名认证通过时的结算卡信息
	 * @param custom_uuid
	 * @return
	 */
	public Map getCustomBankcardInfoPass(String custom_uuid){
		StringBuffer sb = new StringBuffer();
		sb.append("select t1.bankcard_uuid,t1.custom_uuid,t1.super_bank_nm,t1.settle_bank_no,t1.bank_nm,")
			.append(" t1.settle_acct,t1.acct_nm,t2.area_cn_nm as \"bank_province\", t3.area_cn_nm as \"bank_locality\", ")
			.append(" t1.acct_attr,t1.audit_st,t1.rec_st,t1.rec_id from ").append(Table.T_APP_CUSTOM_BANKCARD)
			.append(" t1 LEFT JOIN "+Table.T_APP_AREA_CD+" t2 on t1.bank_province = t2.area_cd ")
			.append(" left join "+Table.T_APP_AREA_CD+" t3 on t1.bank_locality = t3.area_cd")
			.append(" where t1.custom_uuid = ? and t1.audit_st in ("+SystemConstant.REVIEW_ST_1+") and t1.acct_attr = ? and t1.rec_st != ?");
		List list = new ArrayList();
		list.add(custom_uuid);
		list.add(SystemConstant.ACCT_ATTR_0);
		list.add(SystemConstant.REC_ST_0);
		List<Map<String, Object>> result = appCustomBankcardDao.queryForList(sb.toString(), list);
		
		if (!CollectionUtil.isEmpty(result)) {
			return result.get(0);
		} else {
			return null;
		}
	}
	
	/**
	 * 获取实名认证退回修改的结算卡信息
	 * @param custom_uuid
	 * @return
	 */
	public Map getCustomBankcardInfoBack(String custom_uuid){
		StringBuffer sb = new StringBuffer();
		sb.append("select * from (select t1.bankcard_uuid,t1.custom_uuid,t1.super_bank_nm,t1.settle_bank_no,t1.bank_nm,")
			.append(" t1.settle_acct,t1.acct_nm,t2.area_cn_nm as \"bank_province\", t3.area_cn_nm as \"bank_locality\", ")
			.append(" t1.acct_attr,t1.audit_st,t1.rec_st,t1.rec_id from ").append(Table.T_APP_CUSTOM_BANKCARD)
			.append(" t1 LEFT JOIN "+Table.T_APP_AREA_CD+" t2 on t1.bank_province = t2.area_cd ")
			.append(" left join "+Table.T_APP_AREA_CD+" t3 on t1.bank_locality = t3.area_cd")
			.append(" where t1.custom_uuid = ? and t1.audit_st in ("+SystemConstant.REVIEW_ST_2+") and t1.acct_attr = ? and t1.rec_st != ? ")
			.append(" ORDER BY t1.rec_upd_ts desc ) where rownum <= 1");
		List list = new ArrayList();
		list.add(custom_uuid);
		list.add(SystemConstant.ACCT_ATTR_0);
		list.add(SystemConstant.REC_ST_0);
		List<Map<String, Object>> result = appCustomBankcardDao.queryForList(sb.toString(), list);
		if (!CollectionUtil.isEmpty(result)) {
			return result.get(0);
		} else {
			return null;
		}
	}
	/**
	 * 获取结算变更前的结算卡信息
	 * @param bankcard_uuid
	 * @return
	 */
	public Map getOldCustomBankcardInfo(String bankcard_uuid){
		StringBuffer sb = new StringBuffer();
		List<String> list = new ArrayList<>();
		sb.append(" select t1.bankcard_uuid,t1.custom_uuid,t1.super_bank_nm,t1.settle_bank_no,t1.bank_nm,")
			.append(" t1.settle_acct,t1.acct_nm,t2.area_cn_nm as \"bank_province\", t3.area_cn_nm as \"bank_locality\", ")
			.append(" t1.acct_attr,t1.audit_st,t1.rec_st,t1.rec_id from ").append(Table.T_APP_CUSTOM_BANKCARD)
			.append(" t1 LEFT JOIN "+Table.T_APP_AREA_CD+" t2 on t1.bank_province = t2.area_cd ")
			.append(" left join "+Table.T_APP_AREA_CD+" t3 on t1.bank_locality = t3.area_cd").append(" where t1.bankcard_uuid in (select change_bankcard_uuid from ")
			.append(Table.T_APP_CUSTOM_BANKCARD).append(" where BANKCARD_UUID = ? )");
		list.add(bankcard_uuid);
		List<Map<String, Object>> result = appCustomBankcardDao.queryForList(sb.toString(), list);
		if (!CollectionUtil.isEmpty(result)) {
			return result.get(0);
		} else {
			return null;
		}
	}
	
	/**
	 * 获取变更的结算卡信息
	 * @param bankcard_uuid
	 * @return
	 */
	public Map getNewestCustomBankcardInfo(String bankcard_uuid){
		StringBuffer sb = new StringBuffer();
		List<String> list = new ArrayList<>();
		sb.append(" select t1.bankcard_uuid,t1.custom_uuid,t1.super_bank_nm,t1.settle_bank_no,t1.bank_nm,")
		.append(" t1.settle_acct,t1.acct_nm,t2.area_cn_nm as \"bank_province\", t3.area_cn_nm as \"bank_locality\", ")
		.append(" t1.acct_attr,t1.audit_st,t1.rec_st,t1.rec_id from ").append(Table.T_APP_CUSTOM_BANKCARD)
		.append(" t1 LEFT JOIN "+Table.T_APP_AREA_CD+" t2 on t1.bank_province = t2.area_cd ")
		.append(" left join "+Table.T_APP_AREA_CD+" t3 on t1.bank_locality = t3.area_cd")
		.append(" where t1.BANKCARD_UUID = ? and t1.rec_st != ?");
		list.add(bankcard_uuid);
		list.add(SystemConstant.REC_ST_0);
		List<Map<String, Object>> result = appCustomBankcardDao.queryForList(sb.toString(), list);
		if (!CollectionUtil.isEmpty(result)) {
			return result.get(0);
		} else {
			return null;
		}
	}

	public Map getCustomUUIDByUUID(String bankcard_uuid) {
		StringBuffer sb = new StringBuffer();
		List<String> list = new ArrayList<>();
		sb.append("select * from ");
		sb.append(Table.T_APP_CUSTOM_INF);
		sb.append(" where CUSTOM_UUID in (SELECT CUSTOM_UUID from ");
		sb.append(Table.T_APP_CUSTOM_BANKCARD);
		sb.append(" where BANKCARD_UUID = ?)");
		list.add(bankcard_uuid);

		return appCustomBankcardDao.queryForMap(sb.toString(), list);
	}

	/**
	 * 获取银行卡uuid
	 * @param custom_uuid
	 * @return
	 */
	/*public String getBankUuid(String custom_uuid) {
		StringBuffer sb = new StringBuffer();
		sb.append("select BANKCARD_UUID from ");
		sb.append(Table.T_APP_CUSTOM_BANKCARD);
		sb.append(" where custom_uuid = ? ");
		List<String> params = new ArrayList<>();
		params.add(custom_uuid);
		List<Map<String, String>> resultList = appCustomBankcardDao.queryForList(sb.toString(), params);
		if(resultList !=null && resultList.size() > 0 && resultList.get(0) != null && resultList.get(0).get("BANKCARD_UUID") != null){
			return resultList.get(0).get("BANKCARD_UUID");
		}
		return "";
	}*/

	/**
	 * 获取我的银行卡信息
	 *
	 * @param login_id
	 * @param random_number
	 * @param sign
	 * @param custom_no
	 * @param gpsEntity
	 * @return
	 */
	public List getMyBankCard(String login_id, String random_number, String sign, String custom_no, GpsEntity gpsEntity) {
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append("select t1.bankcard_uuid as \"bankcard_uuid\", t1.super_bank_no  as \"super_bank_no\",t1.super_bank_nm  as \"super_bank_nm\",t1.settle_bank_no  as \"settle_bank_no\",concat(rpad(substr( t1.settle_acct,1 , 6 ),length( t1.settle_acct )-4,'*') , substr( t1.settle_acct , length( t1.settle_acct)-3 , 4 ) )  as \"settle_acct\",t1.acct_nm  as \"acct_nm\",t1.bank_nm as \"bank_nm\",t1.bank_province  as \"bank_province\",t1.bank_locality as \"bank_locality\",t1.acct_attr as \"acct_attr\",t1.audit_st as \"audit_st\",t1.audit_desc as \"audit_desc\"")
				.append(" from " + Table.T_APP_CUSTOM_BANKCARD + " t1 ")
				.append(" left join " + Table.T_APP_CUSTOM_INF + " t2 on t1.custom_uuid = t2.custom_uuid ")
				.append(" where t2.custom_no = ? and t1.rec_st = "+SystemConstant.REC_ST_1)
				.append(" order by t1.rec_crt_ts desc");
		List list = new ArrayList();
		list.add(custom_no);
		List<Map<String, Object>> resultList = appCustomBankcardDao.queryForList(sqlBuf.toString(), list);
		if (CollectionUtil.isEmpty(resultList)) {
			return null;
		}
		return resultList;
	}
	/**
	 * 获取卡bin基本信息
	 * @param
	 * @param
	 * @param
	 * @param
	 * @return
	 */
	public Map getCardBinInf(String credit_card_no){
		Map<String,String> getCardBinInf  = cardBinDao.getCardBinAttr(credit_card_no);
		if(CollectionUtil.isEmpty(getCardBinInf)){
			return  null;
		}else{
			Map<String,String> map = new HashMap<String,String>();
			map.put("super_bank_no",getCardBinInf.get("super_bank_no"));
			map.put("super_bank_name",getCardBinInf.get("super_bank_name"));
			map.put("acct_attr",getCardBinInf.get("crd_nature"));
			return map;
		}
	}

	/**
	 * 获取证件号
	 * @param custom_no
	 * @return
	 */
	public Map getCertifiCateNo(String custom_no){
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append(" select t1.certificate_no,t1.custom_uuid,t1.name from  " +Table.T_APP_CUSTOM_INF +" t1 ")
				.append(" where t1.custom_no = ? and t1.rec_st = 1 ");
		List<String> paramList = new ArrayList<>();
		paramList.add(custom_no);
		 return appCustomBankcardDao.queryForMap(sqlBuf.toString(),paramList);
	}

	/**
	 * 	新增信用卡
	 * @param custom_no
	 * @param credit_card_no
	 * @param credit_phone
	 * @param cvv2
	 * @param crd_exp
	 * @return
	 * @throws Exception
	 */
	public String saveCreditCard(HttpServletRequest request,String login_id, String custom_no, String credit_card_no, String credit_phone, String cvv2, String crd_exp) throws  Exception {
		//获取卡BIN信息
		Map getCardBinInfMap = this.getCardBinInf(credit_card_no);
		System.out.println("获取卡BIN信息{}，"+getCardBinInfMap);
		String super_bank_no = String.valueOf(getCardBinInfMap.get("super_bank_no"));
		String super_bank_name = String.valueOf(getCardBinInfMap.get("super_bank_name"));
		String temp_acct_attr = String.valueOf(getCardBinInfMap.get("acct_attr"));
		String acct_attr = SystemConstant.ACCT_ATTR_1;
		temp_acct_attr = acct_attr ;
		//证件号
		Map  cuntomInfMap = this.getCertifiCateNo(custom_no);
		String certificate_no =String.valueOf( cuntomInfMap.get("certificate_no"));
		String custom_uuid = String.valueOf(cuntomInfMap.get("custom_uuid"));
		String acct_nm = String.valueOf(cuntomInfMap.get("name"));
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append(" INSERT INTO  "+ Table.T_APP_CUSTOM_BANKCARD)
				.append(" ( BANKCARD_UUID, ")
				.append(" CUSTOM_UUID, ")
				.append(" SUPER_BANK_NM, ")
				.append(" SUPER_BANK_NO, ")
				.append(" PHONE, ")
				.append(" CERTIFICATE_NO, ")
				.append(" SETTLE_ACCT, ")
				.append(" CVV2, ")
				.append(" ACCT_ATTR, ")
				.append(" CRD_EXP ,  ")
				.append(" auth_bank_card_st ,  ")
				.append(" rec_crt_ts ,  ")
				.append(" rec_upd_ts ,  ")
				.append(" rec_upd_usr_id ,  ")
				.append(" acct_nm, ")
				.append(" audit_st, ")
				.append(" last_oper_in )  ")
				.append(" VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ");
		List<Object> params = new ArrayList<Object>();
		params.add(IDFactoryUtil.getUUID());//主键
		params.add(custom_uuid);
		params.add(super_bank_name);//总行名称
		params.add(super_bank_no);//总行行号
		params.add(credit_phone);
		params.add(certificate_no);
		params.add(credit_card_no);
		params.add(cvv2);
		params.add(acct_attr);//卡性质
		params.add(crd_exp);
		params.add(SystemConstant.AUTH_BANK_CARD_ST_0);
		params.add(new Timestamp(System.currentTimeMillis()));
		params.add(new Timestamp(System.currentTimeMillis()));
		params.add(login_id);//当前用户
		params.add(acct_nm);//用户名
		params.add(SystemConstant.REVIEW_ST_1);
		params.add(SystemConstant.OPER_ADD);//操作标志
		//插入信用卡信息
		appCustomerInfoDao.insertCustomParm(sqlBuf.toString(), params);
		return  SystemConstant.RSP_CODE;
	}

	/**
	 * 是否已有别人使用此卡信息
	 * @param custom_uuid 会员uuid
	 * @param settle_acct 卡号
	 * @return 返回true表示已经有保存的；返回false表示没有保存的
	 */
	public boolean alreadySaveCardInfo(String custom_uuid,String settle_acct){
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append(" SELECT BANKCARD_UUID ")
				.append("   FROM  "+Table.T_APP_CUSTOM_BANKCARD)
				.append("    WHERE SETTLE_ACCT = ? ")
				.append("    AND CUSTOM_UUID != ? ")
				.append("    AND REC_ST != '0' ");
		List<String> params = new ArrayList<>();
		params.add(settle_acct);
		params.add(custom_uuid);
		List<Map<String,Object>> list = appCustomBankcardDao.queryForList(sqlBuf.toString(), params);
		if(list!=null && list.size() > 0){
			return true;
		}else{
			return false;
		}
	}

	/**
	 * 保存会员绑定银行卡信息
	 * @param entity
	 * @param map
	 * @return 返回新增或修改的entity的uuid
	 */
	public String saveCustomBankcard(App_Custom_BankcardEntity entity,Map map){
		//以是否有uuid来区分操作
		if (!StringUtils.isBlank(entity.getBankcard_uuid())) {
			//更新
			entity = (App_Custom_BankcardEntity) this.saveCommonData(entity,map,"update");
			List noChangeList = new ArrayList();
			noChangeList.add("bankcard_uuid");
			noChangeList.add("rec_crt_ts");
			appCustomBankcardDao.update(entity,noChangeList);
		}else{
			//新增
			entity = (App_Custom_BankcardEntity) this.saveCommonData(entity, map, null);
			entity.setBankcard_uuid(IDFactoryUtil.getUUID());
			appCustomBankcardDao.save(entity);
		}
		return entity.getBankcard_uuid();
	}

	/**
	 * 实名认证查询时 加载会员银行卡信息
	 * @param custom_uuid
	 * @param acct_attr
	 * @return
	 */
	public App_Custom_BankcardEntity getBankcardEntity(String custom_uuid,String acct_attr){
		if(SystemConstant.ACCT_ATTR_0.equals(acct_attr)){ //借记卡账户
			Object[] param1 = new Object[]{"custom_uuid","acct_attr","rec_st"};
			Object[] param2 = new Object[]{custom_uuid,acct_attr,SystemConstant.REC_ST_1};
			return appCustomBankcardDao.findByParamses(param1,param2);
		}else if(SystemConstant.ACCT_ATTR_1.equals(acct_attr)){ //信用卡账户
			Object[] param1 = new Object[]{"custom_uuid","acct_attr","auth_bank_card_st"};
			Object[] param2 = new Object[]{custom_uuid,acct_attr,SystemConstant.AUTH_BANK_CARD_ST_1};
			return appCustomBankcardDao.findByParamses(param1,param2);
		}
		return null;
	}

	/**
	 * 变更结算卡(要加载记录有效的且审核状态不是1的)
	 * @param custom_uuid
	 * @param acct_attr
	 * @return
	 */
	public App_Custom_BankcardEntity getBankcardEntityForChangeCard(String custom_uuid,String acct_attr){
		App_Custom_BankcardEntity entity = null;
		//先查变更后待审核的
		Object[] param1 = new Object[]{"custom_uuid","acct_attr","audit_st"};
		Object[] param2 = new Object[]{custom_uuid,acct_attr,SystemConstant.REVIEW_ST_0};
		entity = appCustomBankcardDao.findByParamses(param1,param2);
		if(entity != null){
			return entity;
		}else {
			//再查变更后退回修改的
			param2 = new Object[]{custom_uuid,acct_attr,SystemConstant.REVIEW_ST_2};
			entity = appCustomBankcardDao.findByParamses(param1,param2);
			return entity;
		}
	}

	/**
	 * 校验信用卡是否存在
	 * @param credit_card_no
	 * @return
	 */
	public String getCredit_card_no(String credit_card_no){
		StringBuffer sb = new StringBuffer();
		sb.append("select t1.settle_acct from " + Table.T_APP_CUSTOM_BANKCARD + " t1 where settle_acct = ? and rec_st='1' order by rec_id desc");
		List list = new ArrayList();
		list.add(credit_card_no);
		List<Map<String, Object>> result = appCustomBankcardDao.queryForList(sb.toString(), list);
		if (CollectionUtil.isEmpty(result)) {
			return null;
		} else {
			Map loginIdMap = result.get(0);
			String is_credit_card_no = String.valueOf(loginIdMap.get("login_id"));
			return is_credit_card_no;
		}
	}

	/**
	 * 查询会员实名认证信息
	 * @param custom_no
	 * @param custom_uuid
	 * @return
	 */
	public Map getCustomRealNameInfo(String custom_no, String custom_uuid) {
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append(" select a.certificate_no as \"certificate_no\", ")
				.append("        a.name as \"name\", ")
				.append("        a.super_bank_no as \"super_bank_no\", ")
				.append("        a.super_bank_nm as \"super_bank_nm\", ")
				.append("        a.bank_province as \"bank_province\", ")
				.append("        (select area_cn_nm from t_app_area_cd where area_cd = a.bank_province) as \"bank_province_nm\", ")
				.append("        a.bank_locality as \"bank_locality\", ")
				.append("        (select area_cn_nm from t_app_area_cd where area_cd = a.bank_locality) as \"bank_locality_nm\", ")
				.append("        a.settle_bank_no as \"settle_bank_no\", ")
				.append("        a.bank_nm as \"bank_nm\", ")
				.append("        a.settle_acct as \"settle_acct\", ")
				.append("        a.acct_nm as \"acct_nm\", ")
				.append("        b.settle_acct as \"credit_card_no\", ")
				.append("        a.phone as \"settle_phone\", ")//借记卡银行预留号
				.append("        b.phone as \"credit_phone\", ") //信用卡银行预留号settle_phone
				.append("        b.cvv2 as \"cvv2\", ")
				.append("        b.crd_exp as \"crd_exp\" ")
				.append("   from (select t1.custom_uuid, ")
				.append("                t1.certificate_no, ")
				.append("                t1.name, ")
				.append("                t2.super_bank_no, ")
				.append("                t2.super_bank_nm, ")
				.append("                t2.bank_province, ")
				.append("                t2.bank_locality, ")
				.append("                t2.settle_bank_no, ")
				.append("                t2.bank_nm, ")
				.append("                t2.phone, ")
				.append("                t2.settle_acct, ")
				.append("                t2.acct_nm ")
				.append("           from "+Table.T_APP_CUSTOM_INF+" t1 ")
				.append("           left join "+Table.T_APP_CUSTOM_BANKCARD+" t2 ")
				.append("             on t1.custom_uuid = t2.custom_uuid ")
				.append("          where t2.ACCT_ATTR = '"+SystemConstant.ACCT_ATTR_0+"' AND t2.REC_ST != '0') a ")//借记卡
				.append("   left join (select t1.custom_uuid, t2.settle_acct, t2.phone,t2.cvv2,t2.crd_exp ")
				.append("                from "+Table.T_APP_CUSTOM_INF+" t1 ")
				.append("                left join "+Table.T_APP_CUSTOM_BANKCARD+" t2 ")
				.append("                  on t1.custom_uuid = t2.custom_uuid ")
				.append("               where t2.ACCT_ATTR =  "+SystemConstant.ACCT_ATTR_1)
				.append("                 and t2.auth_bank_card_st = "+SystemConstant.AUTH_BANK_CARD_ST_1+") b ")//信用卡
				.append("     on a.custom_uuid = b.custom_uuid " )
				.append(" where a.custom_uuid = ? ");
		List params = new ArrayList();
		params.add(custom_uuid);
		List<Map<String, Object>> result = appCustomBankcardDao.queryForList(sqlBuf.toString(), params);
		if (!CollectionUtil.isEmpty(result)) {
			return result.get(0);
		} else {
			return new HashMap();
		}
	}

	/**
	 * 查询变更结算卡详细信息
	 * @return
	 */
	public Map getModifySettleBank(String bankcard_uuid) {
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append(" select a.super_bank_no as \"super_bank_no\",  ")
				.append("       a.super_bank_nm as \"super_bank_nm\",  ")
				.append("       a.bank_province as \"bank_province\",  ")
				.append("       (select area_cn_nm from t_app_area_cd where area_cd = a.bank_province) as \"bank_province_nm\",  ")
				.append("       a.bank_locality as \"bank_locality\",  ")
				.append("       (select area_cn_nm from t_app_area_cd where area_cd = a.bank_locality) as \"bank_locality_nm\",  ")
				.append("       a.settle_bank_no as \"settle_bank_no\",  ")
				.append("       a.bank_nm as \"bank_nm\",  ")
				.append("       a.settle_acct as \"settle_acct\",  ")
				.append("       a.phone as \"settle_phone\",  ")
				.append("       a.acct_nm as \"acct_nm\", ")
				.append("       a.audit_st as \"audit_st\" ")
				.append("  from T_APP_CUSTOM_BANKCARD a where a.bankcard_uuid = ? ");
		List params = new ArrayList();
		params.add(bankcard_uuid);
		List<Map<String, Object>> result = appCustomBankcardDao.queryForList(sqlBuf.toString(), params);
		if (!CollectionUtil.isEmpty(result)) {
			return result.get(0);
		} else {
			return new HashMap();
		}
	}
	
	/**
	 * 更新银行卡状态
	 * @param audit_st
	 * @param bankcard_uuid
	 */
	public void updateCardState(String audit_st,String user,String bankcard_uuid,String audit_desc){
		StringBuffer sb = new StringBuffer();
		List list = new ArrayList<>();
		sb.append(" update ").append(Table.T_APP_CUSTOM_BANKCARD).append(" set last_oper_in = ?,")
		.append(" audit_st = ?,rec_upd_ts = ?,rec_upd_usr_id = ?,audit_desc=? where bankcard_uuid = ?");
		list.add(SystemConstant.OPER_UPDATE);
		list.add(audit_st);
		list.add(new Timestamp(System.currentTimeMillis()));
		list.add(user);
		list.add(audit_desc);
		list.add(bankcard_uuid);
		
		appCustomBankcardDao.update(sb.toString(), list);
	}

	/**
	 *	根据uuid查询信用卡详细信息
	 * @param bankcard_uuid
	 * @return
	 */
	public Map getBankcardInfoByUUID(String bankcard_uuid){
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append(" select settle_acct       as \"credit_card_no\", ")
				.append("        phone             as \"credit_phone\", ")
				.append("        super_bank_no     as \"super_bank_no\", ")
				.append("        super_bank_nm     as \"super_bank_nm\", ")
				.append("        cvv2              as \"cvv2\", ")
				.append("        crd_exp           as \"crd_exp\", ")
				.append("        nvl(auth_bank_card_st,0) as \"auth_bank_card_st\" ")
				.append("   from  "+Table.T_APP_CUSTOM_BANKCARD)
				.append("  where rec_st != '0' ")
				.append("  	 and acct_attr = '"+ SystemConstant.ACCT_ATTR_1 +"' ")
				.append("    and bankcard_uuid = ? ");
		List params = new ArrayList();
		params.add(bankcard_uuid);
		List<Map<String, Object>> result = appCustomBankcardDao.queryForList(sqlBuf.toString(), params);
		if(result != null && result.size() > 0){
			return result.get(0);
		}else{
			return new HashMap();
		}
	}

	/**
	 * 修改cvv2及crd_exp
	 * @param bankcard_uuid
	 * @param cvv2
	 * @param crd_exp
	 */
	public void updateCustomBankCard(String bankcard_uuid,String cvv2,String crd_exp){
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append(" update  "+Table.T_APP_CUSTOM_BANKCARD)
				.append("    set cvv2 = ?, crd_exp = ? ,")
				.append("    rec_upd_ts = SYSTIMESTAMP, ")
				.append("     last_oper_in = '"+SystemConstant.OPER_UPDATE+"' ")
				.append("  where bankcard_uuid = ? ");
		List params = new ArrayList();
		params.add(cvv2);
		params.add(crd_exp);
		params.add(bankcard_uuid);
		appCustomBankcardDao.update(sqlBuf.toString(), params);
	}

	/**
	 * 解绑银行卡
	 * @param bankcard_uuid
	 */
	public void unbindCustomBankCard(String bankcard_uuid){
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append(" update  "+Table.T_APP_CUSTOM_BANKCARD)
				.append("    set rec_st = '"+SystemConstant.REC_ST_0+"', ")
				.append("     REC_UPD_TS = SYSTIMESTAMP,  ")
				.append("     last_oper_in = '"+SystemConstant.OPER_DEL+"' ")
				.append("  where bankcard_uuid = ? ");
		List params = new ArrayList();
		params.add(bankcard_uuid);
		appCustomBankcardDao.update(sqlBuf.toString(), params);
	}
}
